/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webbucks;

import javax.ejb.Stateless;
import javax.ejb.LocalBean;
import java.util.*;
import java.sql.*;
import java.io.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

/**
 *
 * @author scartissue
 */
@Stateless
@LocalBean
public class CustomerBean {
    
    private String username;
    private String password;
    private String email;
    private String firstName;
    private String lastName;
    private String personalNumber;
    private String street;
    private String city;
    private int    postalNumber;
    private boolean auth = false;
    
    private String url = "jdbc:mysql://localhost:8889/";
    private String dbName = "webbucks";
    private String driver = "com.mysql.jdbc.Driver";
    private String user = "root"; 
    private String pw = "root";
    


    // constructor, set the database URL
    public CustomerBean() {
    }
    
    public CustomerBean(String usr) {
        
    }
    
     
    public void setUsername( String name ) {
        username = name;
    }
    
    public void setPassword( String pass ) {
        password = pass;
    }
        
    public void setEmail( String mail ) {
        email = mail;
    }
    
    public void setFirstName( String fname ) {
        firstName = fname;
    }
    
    public void setLastName( String lname ) {
        lastName = lname;
    }
    
    public void setPersonalNumber( String number ) {
        personalNumber = number;
    }
    
    public void setStreet( String str ) {
        street = str;
    }
    
    public void setCity( String c ) {
        city = c;
    }
    
    public void setPostalNumber( int number ) {
        postalNumber = number;
    }
    
    
    public String getUsername() {
        return username;
    }
    
    public String getPassword() {
        return password;
    }
    

    public String getEmail() {
        return email;
    }
    
    public String getFirstName() {
        return firstName;
    }
    
    public String getLastName() {
        return lastName;
    }
    
    public String getPersonalNumber() {
        return personalNumber;
    }
    
    public String getStreet() {
        return street;
    }

    
    public String getCity() {
        return city;
    }
    
    public int getPostalNumber() {
        return postalNumber;
    }
    
    public boolean isAuthenticated() {
        return auth;
    }
    
    public boolean isUnique(String usr) {
        boolean r=false;
        
        String Query="SELECT username FROM customer where username=?";        
        ResultSet rs=null;
        PreparedStatement pstmt=null;
        Connection con=null;                

        try {
            Class.forName(driver).newInstance();
            con=DriverManager.getConnection(url+dbName,user,pw);
            
            pstmt=con.prepareStatement(Query);
            pstmt.setString(1, usr);
            rs=pstmt.executeQuery();                
            
            r= !(rs.next()); 
        } catch( Exception e ){
            System.out.println("Something went wrong" + e.toString() );
        } finally {
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
            if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) {}
            if (con != null) try { con.close(); } catch (SQLException ignore) {}
        }    
        return r;
    }
    
    public CustomerBean authCustomer(String usr, String pass) {
        CustomerBean cb = null;
     
        String Query= "SELECT username FROM customer WHERE username=? AND password=?";        
        ResultSet rs=null;
        PreparedStatement pstmt=null;
        Connection con=null;                

        try {
            Class.forName(driver).newInstance();
            con=DriverManager.getConnection(url+dbName,user,pw);
            
            pstmt=con.prepareStatement(Query);
            pstmt.setString(1, usr);
            pstmt.setString(2, pass);
            rs=pstmt.executeQuery();                
            
           
            if (rs.next()){
                //populate bean with user values
                cb = setCustomer(usr);
                auth = true;
            }
            else{
                auth = false;
            }
            
            
        } catch( Exception e ){
            System.out.println("Something went wrong" + e.toString() );
        } 
        finally {
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
            if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) {}
            if (con != null) try { con.close(); } catch (SQLException ignore) {}
        }    
        return cb;
    }
    
    public CustomerBean addCustomer(HttpServletRequest request, HttpSession sess) {
        String usr=request.getParameter("user");
        String pass1=request.getParameter("password");
        String email= request.getParameter("email");
        String fn=request.getParameter("firstName");
        String ln= request.getParameter("lastName");
        String pnum= request.getParameter("persNumber");
        String st=request.getParameter("street");
        String city=request.getParameter("city");
        String postnum=request.getParameter("zip");
    
        String q = "INSERT INTO customer(username, password, email, firstname, "
                    + "lastname, personalnumber, street,  city, postalnumber) "
                    + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
        ResultSet rs=null;
        PreparedStatement pstmt=null;
        Connection con=null;                

        try {
            Class.forName(driver).newInstance();
            con=DriverManager.getConnection(url+dbName,user,pw);
            
            pstmt=con.prepareStatement(q);
            pstmt.setString(1, usr);
            pstmt.setString(2, pass1);
            pstmt.setString(3, email);
            pstmt.setString(4, fn);
            pstmt.setString(5, ln);
            pstmt.setString(6, pnum);
            pstmt.setString(7, st);
            pstmt.setString(8, city);
            pstmt.setString(9, postnum);
            
            
            int r = pstmt.executeUpdate();
            pstmt.close();
            System.out.println("addUser updated "+r+" rows.");
            
           //Statement stmt = con.createStatement();
            //q = "INSERT INTO orderstatus (customer) VALUES(\""+usr+"\")";
            //System.out.println(q);
            //stmt.executeUpdate(q);
            //stmt.close();
            
            return this.setCustomer(usr);

        } catch( Exception e ){
            System.out.println("Something went wrong" + e.toString() );
        } finally {
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
            if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) {}
            if (con != null) try { con.close(); } catch (SQLException ignore) {}
        }
        
        
        return null;
    }
    
    
        public CustomerBean updateCustomer(HttpServletRequest request, HttpSession sess,String pass1) {
        String email= request.getParameter("email");
        String fn=request.getParameter("firstName");
        String ln= request.getParameter("lastName");
        String pnum= request.getParameter("persNumber");
        String st=request.getParameter("street");
        String city=request.getParameter("city");
        String postnum=request.getParameter("zip");
    
        String q = "UPDATE customer SET password=?, email=?, firstName=?,"
                +  "lastName=?, personalnumber=?, street=?, city=?, postalnumber=? WHERE username=?";
                
        ResultSet rs=null;
        PreparedStatement pstmt=null;
        Connection con=null;                

        try {
            Class.forName(driver).newInstance();
            con=DriverManager.getConnection(url+dbName,user,pw);
            
            pstmt=con.prepareStatement(q);
            pstmt.setString(1, pass1);
            pstmt.setString(2, email);
            pstmt.setString(3, fn);
            pstmt.setString(4, ln);
            pstmt.setString(5, pnum);
            pstmt.setString(6, st);
            pstmt.setString(7, city);
            pstmt.setString(8, postnum);
            pstmt.setString(9, getUsername());
            
            
            int r = pstmt.executeUpdate();
            System.out.println("addUser updated "+r+" rows.");
            
            return this.setCustomer(getUsername());

        } catch( Exception e ){
            System.out.println("Something went wrong" + e.toString() );
        } finally {
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
            if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) {}
            if (con != null) try { con.close(); } catch (SQLException ignore) {}
        }
        return null;
    }
    
    
    //populate bean with user values
    public CustomerBean setCustomer(String usr) {

        ResultSet rs=null;
        PreparedStatement pstmt=null;
        Connection con=null;                

        String q = "SELECT password, email, firstname, lastname, "
                        +"personalnumber, street,  city, postalnumber "
                + "FROM customer WHERE username = ?";

        try {
            Class.forName(driver).newInstance();
            con=DriverManager.getConnection(url+dbName,user,pw);

            pstmt=con.prepareStatement(q);
            pstmt.setString(1, usr);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                this.setUsername(usr);
                this.setPassword(rs.getString(1));
                this.setEmail(rs.getString(2));
                this.setFirstName(rs.getString(3));
                this.setLastName(rs.getString(4));
                this.setPersonalNumber(rs.getString(5));
                this.setStreet(rs.getString(6));
                this.setCity(rs.getString(7));
                this.setPostalNumber(Integer.parseInt(rs.getString(8)));
                auth=true;
                return this;
            } else {
                throw new SQLException("db get error...");
            }

        } catch( Exception e ){
            System.out.println("Something went wrong " + e.toString() );
        } finally {
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
            if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) {}
            if (con != null) try { con.close(); } catch (SQLException ignore) {}
        }            
        return null;
    }
        
        
}
    

    
    
    
    
    
    

